USE master;
GO

-- configure blocked process threshold
EXECUTE sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXECUTE sys.sp_configure 'blocked process threshold', 30;
RECONFIGURE;
GO
EXECUTE sys.sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

-- create a queue
DECLARE
    @ReturnCode INT,
    @TraceID INT,
    @TraceName NVARCHAR(245) = N'C:\Temp\BPR_' +
        CONVERT(SYSNAME, SERVERPROPERTY('MachineName')),
    @MaxFileSize BIGINT = 200,
	@FileCount INT = 5;

-- the .trc extension will be appended to the filename automatically
EXECUTE @ReturnCode = sp_trace_create @TraceID OUTPUT, 2, @TraceName, @MaxFileSize, NULL, @FileCount;

-- set the events
EXECUTE sp_trace_setevent @TraceID, 137, 1, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 3, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 4, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 12, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 13, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 14, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 22, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 15, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 24, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 26, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 32, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 41, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 51, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 60, 1;
EXECUTE sp_trace_setevent @TraceID, 137, 64, 1;

-- set the trace status to start
EXECUTE sp_trace_setstatus @TraceID, 1;

-- display trace id and trace name
SELECT TraceID = @TraceID, TraceFile = @TraceName + N'.trc';

-- monitor the workload

-- view running traces on the system
SELECT * FROM sys.traces;

-- get trace event info
SELECT 
 	e.name AS event_name, 
 	c.name AS column_name
FROM fn_trace_geteventinfo(<traceid>) i
INNER JOIN sys.trace_events e ON i.eventid = e.trace_event_id 
INNER JOIN sys.trace_columns c ON i.columnid = c.trace_column_id;

-- stop the trace, but keep its definition on the server
EXECUTE sp_trace_setstatus <traceid>, 0;

-- read trace file
SELECT
	e.name AS EventName,
	t.DatabaseID,
	t.Duration,
	t.StartTime,
	t.EndTime,
	t.ServerName,
	t.SessionLoginName,
	t.SPID,
	t.TransactionID,
	t.TextData,
	t.IsSystem,
	t.ObjectID,
	t.IndexID,
	t.Mode
FROM ::fn_trace_gettable('C:\Temp\BPR_<machine_name>.trc', default) t
INNER JOIN sys.trace_events e ON t.EventClass = e.trace_event_id
WHERE t.EventClass = 137            -- 137 Blocked process report
-- e.name = 'Blocked process report'
-- AND t.Duration > 100 * POWER(10, 6) -- 100 s
ORDER BY t.Duration DESC;

-- close the trace and delete its definition from the server
EXECUTE sys.sp_trace_setstatus <traceid>, 2;

-- cleanup
EXECUTE sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXECUTE sys.sp_configure 'blocked process threshold', 0;
RECONFIGURE;
GO
EXECUTE sys.sp_configure 'show advanced options', 0;
RECONFIGURE;
GO